﻿using MySql.Data.MySqlClient;
using System;
using System.Data;

namespace HirelSeriesH.Tools
{
    public class DBHelper
    {
        //数据库连接字符串
        private static readonly string connstr = "server=127.0.0.1;port=3306;user=root;password=ijsfmiusc;database=ptc-shanghaijiewei;pooling=false;charset=utf8;SslMode=None";
        //private static readonly string connstr = "server=127.0.0.1;port=3306;user=root;password=123;database=daw-ptc;pooling=false;charset=utf8;SslMode=None";

        /// <summary>
        /// 执行插入、删除、更改语句。执行成功返回受影响的数据的行数，返回1可做true判断。执行失败不返回任何数据，报错，下面代码都不执行。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, int cmdType, params MySqlParameter[] paras)
        {
            int count = 0;
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                MySqlCommand command = new MySqlCommand(sql, conn);
                if (cmdType == 2)
                {
                    command.CommandType = CommandType.StoredProcedure;
                }
                if (paras != null && paras.Length > 0)
                {
                    command.Parameters.AddRange(paras);
                }
                conn.Open();
                count = command.ExecuteNonQuery();
                command.Parameters.Clear();
                conn.Close();
            }
            return count;
        }
        /// <summary>
        /// 执行查询，并返回查询结果集中第一行的第一列。所有其他的列和行将被忽略。select语句无记录返回时，ExecuteScalar()返回NULL值。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, int cmdType, params MySqlParameter[] paras)
        {
            object obj = null;
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                MySqlCommand command = new MySqlCommand(sql, conn);
                if (cmdType == 2)
                {
                    command.CommandType = CommandType.StoredProcedure;
                }
                if (paras != null && paras.Length > 0)
                {
                    command.Parameters.AddRange(paras);
                }
                conn.Open();
                obj = command.ExecuteScalar();
                command.Parameters.Clear();
                conn.Close();
            }
            return obj;
        }

        public static MySqlDataReader ExecuteReader(string sql, int cmdType, params MySqlParameter[] paras)
        {
            MySqlDataReader dataReader = null;
            MySqlConnection conn = new MySqlConnection(connstr);
            MySqlCommand command = new MySqlCommand(sql, conn);
            if (cmdType == 2)
            {
                command.CommandType = CommandType.StoredProcedure;
            }
            if (paras != null && paras.Length > 0)
            {
                command.Parameters.AddRange(paras);
            }
            try
            {
                conn.Open();
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
            }
            catch (MySqlException e)
            {
                conn.Close();
                throw new Exception("ExecuteReader执行异常", e);
            }
            return dataReader;
        }
        /// <summary>
        /// 可以进行多表组合查询。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, int cmdType, params MySqlParameter[] paras)
        {
            DataSet dataSet = new DataSet();
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                MySqlCommand command = new MySqlCommand(sql, conn);
                if (cmdType == 2)
                {
                    command.CommandType = CommandType.StoredProcedure;
                }
                if (paras != null && paras.Length > 0)
                {
                    command.Parameters.AddRange(paras);
                }

                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
                conn.Open();
                dataAdapter.Fill(dataSet);
                command.Parameters.Clear();
                conn.Close();
            }
            return dataSet;
        }

        /// <summary>
        /// 只能进行单表查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, int cmdType, params MySqlParameter[] paras)
        {
            DataTable dataTable = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
                MySqlCommand command = new MySqlCommand(sql, conn);
                if (cmdType == 2)
                {
                    command.CommandType = CommandType.StoredProcedure;
                }
                if (paras != null && paras.Length > 0)
                {
                    command.Parameters.AddRange(paras);
                }

                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
                conn.Open();
                dataAdapter.Fill(dataTable);
                command.Parameters.Clear();
                conn.Close();
            }
            return dataTable;
        }

    }

}
